﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using KCL.Code.Entities;
using System.Globalization;

namespace KCL.Code.Data
{
    public class Search : BaseDAO
    {
        #region Tim Kiem Ton Thanh Pham & Chuyen Thanh Pham

        public DataTable GetAllProductSize()
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT 'All' AS [Size] UNION ALL SELECT ([Size].[length] + ' x ' + [Size].[width]) AS [Size] FROM [Size]");
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable sizeTab = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(sizeTab);
                }
                catch (Exception)
                {
                    return null;
                }
                return sizeTab;
            }
        }

        public DataTable GetAllMaterial()
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT 'All' AS [Material] UNION ALL SELECT [code] AS [Material] FROM [Material] WHERE [isActive] = 1");
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable materialTab = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(materialTab);
                }
                catch (Exception)
                {
                    return null;
                }
                return materialTab;
            }
        }

        public DataTable GetAllDescription()
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT 'All' AS [Description] UNION ALL SELECT [description] AS [Description] FROM [OrderMaterial]");
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(command);
                DataTable desTab = new DataTable();

                try
                {
                    connection.Open();
                    sqlAdapter.Fill(desTab);
                }
                catch (Exception)
                {
                    return null;
                }
                return desTab;
            }
        }

        public int GetTotalUnfinishedProducts(string size, string material, string description, int maximumRows, int startRowIndex, string sortExpression)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT COUNT(*) FROM [Order] WHERE [isFinished] = 0");
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                try
                {
                    connection.Open();
                    return Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public List<UnfinishedProduct> GetUnfinishedProducts(string size, string material, string description, int maximumRows, int startRowIndex, string sortExpression)
        {
            List<UnfinishedProduct> results = new List<UnfinishedProduct>();
            int currentIndex = 0;

            DataTable data = new DataTable();
            //string size = "All";
            //string material = "All";
            //string description = string.Empty;
            if (string.IsNullOrEmpty(description))
            {
                description = string.Empty;
            }

            data = GetUnfinishedProductsTable(size.Trim(), material.Trim(), description.Trim());

            while (currentIndex < data.Rows.Count)
            {
                UnfinishedProduct product = new UnfinishedProduct();
                product.code = data.Rows[currentIndex]["code"].ToString();
                product.name = data.Rows[currentIndex]["name"].ToString();
                product.customerId = Convert.ToInt32(data.Rows[currentIndex]["customerId"]);
                product.description = data.Rows[currentIndex]["description"].ToString();
                product.finishedQuantity = Convert.ToInt32(data.Rows[currentIndex]["finishedQuantity"]);
                product.materialId = data.Rows[currentIndex]["materialId"].ToString();
                product.size = data.Rows[currentIndex]["Size"].ToString();
                product.orderQuantity = Convert.ToInt32(data.Rows[currentIndex]["orderQuantity"]);
                product.orderId = Convert.ToInt32(data.Rows[currentIndex]["orderId"]);
                results.Add(product);
                currentIndex++;
            }
            return results;
        }

        public DataTable GetUnfinishedProductsTable(string size, string material, string description)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_SearchUnfinishedProducts", connection);
                command.CommandType = CommandType.StoredProcedure;

                if (description == null)
                    description = string.Empty;

                command.Parameters.Add(new SqlParameter("@Size", size));
                command.Parameters.Add(new SqlParameter("@Material", material));
                command.Parameters.Add(new SqlParameter("@Description", description));

                SqlDataAdapter dataAdap = new SqlDataAdapter(command);
                DataTable data = new DataTable();

                try
                {
                    connection.Open();
                    dataAdap.Fill(data);
                    return data;
                }
                catch (Exception)
                {
                    return null;
                }
            }
        }

        public bool UpdateExchangeFinishedQuantity(int destinationId, int sourceId, int transferedQuantity)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("UPDATE [OrderMaterial] SET finishedQuantity = finishedQuantity + {0} WHERE id = {1} UPDATE [OrderMaterial] SET finishedQuantity = finishedQuantity - {2} WHERE id = {3}", transferedQuantity, destinationId, transferedQuantity, sourceId);
                SqlCommand command = new SqlCommand(sqlStatement, connection);
                command.CommandType = CommandType.Text;

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }

        #endregion

        #region Bao Cao Tong So Thanh Pham

        public int GetTotalFinishedProducts(string txtFromDate, string txtToDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT COUNT(*) FROM [Order] WHERE [isFinished] = 1 AND [deliveryDate] IS NOT NULL AND [deliveryDate] <= CONVERT(DATE, '{0}', 103) AND CONVERT(DATE, '{1}', 103) <= [deliveryDate]", txtToDate.Trim(), txtFromDate.Trim());
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                try
                {
                    connection.Open();
                    return Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public List<UnfinishedProduct> GetFinishedProducts(string txtFromDate, string txtToDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_StatisticFinishedProducts", connection);
                command.CommandType = CommandType.StoredProcedure;

                DateTime fromDate = DateTime.ParseExact(txtFromDate.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
                DateTime toDate = DateTime.ParseExact(txtToDate.Trim(), "dd/MM/yyyy", CultureInfo.InvariantCulture);

                command.Parameters.Add(new SqlParameter("@fromDate", fromDate));
                command.Parameters.Add(new SqlParameter("@toDate", toDate));

                SqlDataAdapter dataAdap = new SqlDataAdapter(command);
                DataTable data = new DataTable();

                List<UnfinishedProduct> results = new List<UnfinishedProduct>();
                int currentIndex = 0;

                try
                {
                    connection.Open();
                    dataAdap.Fill(data);

                    while (currentIndex < data.Rows.Count)
                    {
                        UnfinishedProduct product = new UnfinishedProduct();
                        product.code = data.Rows[currentIndex]["code"].ToString();
                        product.customerId = Convert.ToInt32(data.Rows[currentIndex]["customerId"]);
                        product.description = data.Rows[currentIndex]["description"].ToString();
                        product.materialId = data.Rows[currentIndex]["materialId"].ToString();
                        product.size = data.Rows[currentIndex]["size"].ToString();
                        product.orderQuantity = Convert.ToInt32(data.Rows[currentIndex]["orderQuantity"]);
                        results.Add(product);
                        currentIndex++;
                    }
                    return results;
                }
                catch (Exception)
                {
                    return null;
                }
            }


        }

        #endregion

        #region Bao Cao Tong So Don Hang

        public int GetTotalOrder(string txtFromDate, string txtToDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT COUNT(*) FROM [Order] WHERE [createdDate] <= CONVERT(DATE, '{0}', 103) AND CONVERT(DATE, '{1}', 103) <= [createdDate]", txtToDate.Trim(), txtFromDate.Trim());
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                try
                {
                    connection.Open();
                    return Convert.ToInt32(command.ExecuteScalar());
                }
                catch (Exception)
                {
                    return 0;
                }
            }
        }

        public List<Order> GetOrders(string txtFromDate, string txtToDate, string sortExpression, int maximumRows, int startRowIndex)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT [Order].[code], [customerId], [name], [createdDate], [totalPrice], [prePay], [remain], [isFinished] FROM [Order], [Customer] WHERE [Order].[customerId] = [Customer].[id] AND [createdDate] <= CONVERT(DATE, '{0}', 103) AND CONVERT(DATE, '{1}', 103) <= [createdDate]", txtToDate.Trim(), txtFromDate.Trim());
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                SqlDataAdapter dataAdap = new SqlDataAdapter(command);
                DataTable data = new DataTable();

                List<Order> results = new List<Order>();
                int currentIndex = 0;

                try
                {
                    connection.Open();
                    dataAdap.Fill(data);

                    while (currentIndex < data.Rows.Count)
                    {
                        Order order = new Order();
                        order.code = data.Rows[currentIndex]["code"].ToString();
                        order.customerId = Convert.ToInt32(data.Rows[currentIndex]["customerId"]);
                        order.customerName = data.Rows[currentIndex]["name"].ToString();
                        order.createdDate = data.Rows[currentIndex]["createdDate"].ToString();
                        order.totalPrice = Convert.ToDouble(data.Rows[currentIndex]["totalPrice"].ToString());
                        order.prePay = Convert.ToInt32(data.Rows[currentIndex]["prePay"]);
                        order.remain = Convert.ToInt32(data.Rows[currentIndex]["remain"]);
                        order.isFinished = (bool)(data.Rows[currentIndex]["isFinished"]);
                        results.Add(order);
                        currentIndex++;
                    }
                    return results;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }


        }

        #endregion

        #region Tim Kiem & Nhap Thanh Pham

        public int GetTotalInput(int orderId, string sortExpression, int maximumRows, int startRowIndex)
        {
            return 1;
        }

        public List<FinishedProduct> GetInput(Int64 orderId, string sortExpression, int maximumRows, int startRowIndex)
        {
            using (SqlConnection connection = GetConnection())
            {
                string sqlStatement = string.Format("SELECT [materialId],[description],[Phase].[id] AS phaseID, [name],[quantity],[orderQuantity],[finishedQuantity], [OrderMaterial].[id] AS OrderMaterialId FROM [Order], [OrderMaterial], [OrderMaterialPhase], [Phase] WHERE [isFinished] = 0 AND [Order].[id] = {0} AND [Order].[id] = [OrderMaterial].[orderId] AND [OrderMaterial].[id] = [OrderMaterialPhase].[orderMaterialId] AND [Phase].[id] = [OrderMaterialPhase].[phaseId]", orderId);                             
                SqlCommand command = new SqlCommand(sqlStatement, connection);

                SqlDataAdapter dataAdap = new SqlDataAdapter(command);
                DataTable data = new DataTable();

                List<FinishedProduct> results = new List<FinishedProduct>();
                int currentIndex = 0;

                try
                {
                    connection.Open();
                    dataAdap.Fill(data);

                    string OrderMaterialId = string.Empty;
                    OrderMaterialId = data.Rows[currentIndex]["OrderMaterialId"].ToString();
                    FinishedProduct product = new FinishedProduct();

                    while (currentIndex <= data.Rows.Count)
                    {
                        if (currentIndex < data.Rows.Count && OrderMaterialId.Equals(data.Rows[currentIndex]["OrderMaterialId"].ToString()))
                        {
                            product.name += data.Rows[currentIndex]["name"].ToString() + "(" + data.Rows[currentIndex]["quantity"].ToString() + "), ";                            
                        }
                        else
                        {
                            product.materialId = data.Rows[currentIndex-1]["materialId"].ToString();
                            product.orderQuantity = Convert.ToInt32(data.Rows[currentIndex-1]["orderQuantity"]);
                            product.finishedQuantity = Convert.ToInt32(data.Rows[currentIndex-1]["finishedQuantity"]);
                            product.description = data.Rows[currentIndex-1]["description"].ToString();
                            product.phaseID = Convert.ToInt32(data.Rows[currentIndex-1]["phaseID"]);
                            product.name = product.name.Substring(0, product.name.Length - 2);
                            results.Add(product);
                            if (currentIndex < data.Rows.Count)
                            {
                                OrderMaterialId = data.Rows[currentIndex]["OrderMaterialId"].ToString();
                            }                            
                            product = new FinishedProduct();
                        }
                        currentIndex++;                      
                    }
                    return results;
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        #endregion
    }
}